package com.hbnu.dataAccesss;

import com.hbnu.dao.UserDao;
import javafx.beans.binding.MapExpression;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.Map;

public class jdbcTemplateCase {
    public static void main(String[] args) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl("jdbc:mysql://localhost:3306/j2ee");
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

//        创建表
        String createTable = "create table if not exists tb_user(name varchar(20),address varchar(40));";
        jdbcTemplate.execute(createTable);

//        DriverManager.registerDriver(new Dri);
//        添加数据
        String insertSql = "insert into tb_user values ('fang','huangshi')";
        String insertSql1 = "insert into tb_user values(?,?)";
        jdbcTemplate.update(insertSql, "huang", "huangshi");

//        删除数据
        String deleteSql = "delete from tb_user where name=?";
        jdbcTemplate.update(deleteSql, "huang");

//      更新数据
        String updateSql = "update tb_user set address=? where name=?";
        jdbcTemplate.update(updateSql, "huangshi CiHi", "huangjinyu");

        jdbcTemplate.update(
                conn -> {
                    String sql = "insert into tb_user value(?,?)";
                    PreparedStatement ps = conn.prepareStatement(sql);
                    ps.setString(1, "huangjinyu");
                    ps.setString(2, "hubei hangshi");
                    return ps;
                }
        );

//        查询
        String querySql = "select name from tb_user where name=?";
        jdbcTemplate.queryForObject(querySql, String.class, "huangjinyu");
//        System.out.println(name);

        String querySql1 = "select name form tb_user";
//        jdbcTemplate.queryForList(querySql1);
        List<Map<String, Object>> result = jdbcTemplate.queryForList(querySql1);
        for (Map<String, Object> map : result
        ) {
            for (String s : map.keySet()
            ) {
                System.out.println(s + "---->" + map.get(s));
            }
        }
        List<String> result1 = jdbcTemplate.queryForList(querySql1, String.class);
        for (String s : result1
        ) {
            System.out.println(s);
        }

        User user = jdbcTemplate.queryForObject("select name,address from tb_user",

                (rs, num) -> {
                    User u = new User();
                    u.setName(rs.getString("name"));
                    return u;
                });
        System.out.println(user);
        User user1 = jdbcTemplate.queryForObject("select name,address from tb_user", new UserMapper());
        System.out.println(user1);

      }

    }
    class User{}


}
